﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

using CoyoEden.Models;

namespace CoyoEden.Providers
{
	public partial class DbPortalProvider:PortalProvider
	{

		#region Albums
		public override Album SelectAlbum(int id)
		{
            
			return new Album();
		}

        public override void InsertAlbum(Album item)
		{
			List<Album> items = Album.Albums;
			items.Add(item);
		}

        public override void UpdateAlbum(Album item)
		{
			List<Album> items = Album.Albums;
			items.Remove(item);
			items.Add(item);

			//update pics
			//TODO:PICS
			var pics = new List<PortalFile>();
			UpldatePics(item,pics);
		}

        public override void DeleteAlbum(Album item)
		{
			List<Album> items = Album.Albums;
			items.Remove(item);

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("DELETE FROM {0}AlbumFile ", tablePrefix);
					sql.AppendFormat("WHERE AlbumID = {0}id;", parmPrefix);
					sql.AppendFormat("DELETE FROM {0}AlbumComment ", tablePrefix);
					sql.AppendFormat("WHERE AlbumID = {0}id;", parmPrefix);
					sql.AppendFormat("DELETE FROM {0}AlbumTag WHERE AlbumID={1}id;",tablePrefix,parmPrefix);
					sql.AppendFormat("DELETE FROM {0}Albums WHERE Id={1}id;",tablePrefix,parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();
				}
			}
		}

        public override List<Album> FillAlbums()
		{
			List<Album> items = new List<Album>();
			List<int> IDs = new List<int>();

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;

				using (DbCommand cmd = conn.CreateCommand())
				{
					string sqlQuery = "SELECT Id FROM " + tablePrefix + "Albums ";
					cmd.CommandText = sqlQuery;
					cmd.CommandType = CommandType.Text;

					conn.Open();

					using (DbDataReader rdr = cmd.ExecuteReader())
					{
						while (rdr.Read())
						{
							IDs.Add(rdr.GetInt32(0));
						}
					}
				}
			}

			foreach (int id in IDs)
			{
				items.Add(Album.Load(id));
			}

			items.Sort();
			return items;
		}
		#endregion

		#region helper methods
		private void LoadAlbumFiles(Album item,DbConnection conn) {
			StringBuilder sql = new StringBuilder();
			sql.AppendFormat("SELECT DISTINCT FileID FROM {0}AlbumFile WHERE AlbumID={1}id",tablePrefix,parmPrefix);
			using(DbCommand cmd=conn.CreateCommand()){//using1
				cmd.CommandText = sql.ToString();
				cmd.CommandType = CommandType.Text;
				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = item.Id;
				cmd.Parameters.Add(dpID);

				using (DbDataReader rdr = cmd.ExecuteReader()) { //using2
					if (rdr.HasRows) { 
						while(rdr.Read()){
							item.Pics.Add(PortalFile.GetPortalFile(rdr.GetInt32(0)));
						}
					}
				}//endof using2
			}//endof using1

		}
		private void UpldatePics(Album item,List<PortalFile> pics) {
            //1,get new items
            var newList = from obj in item.Pics.Distinct()
                          where (null == pics.FirstOrDefault(x => x.Id == obj.Id))
                          select obj.Id;
            //2,get deleted items
            var deletedList = from obj in pics
                              where (null == item.Pics.Distinct().FirstOrDefault(x => x.Id == obj.Id))
                              select obj;
            //3,update items

		}
        private Uri resolveWebsite(string websiteStr) {
            Uri website;
            if (Uri.TryCreate(websiteStr, UriKind.Absolute, out website))
            {
                return website;
            }
            return null;
        }
		#endregion
	}
}
